package rmaqlc31.service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import rmaqlc31.interfaces.SungJukService;
import rmaqlc31.model.SungJukVO;
public class JDBCSungJuk implements SungJukService{

		// JDBC 접속문
		private static final String DRV = "oracle.jdbc.OracleDriver";
		private static final String URL = "jdbc:oracle:thin:@127.0.0.1/xe";
		private static final String USR = "scott";
		private static final String PWD = "TIGER";

		// SQL 문 작성
		private static final String insertSQL 
		 = " INSERT INTO SUNGJUK(SJNO,NAME,KOR,ENG,MAT) VALUES(SJNO.NEXTVAL,?,?,?,?)";
		private static final String selectSQL 
		 = " SELECT * FROM SUNGJUK ORDER BY SJNO DESC";

		private static final String updateSQL = 
				" update sungjuk set name = ?, kor = ?, eng = ?, mat = ? where sjno = ?";
		
		private static final String deleteSQL = 
				" delete from sungjuk where sjno = ? ";
		
		
		@Override
		public boolean newSungJuk(SungJukVO sj) {
			Connection conn = null;
			PreparedStatement ps = null;
			boolean result = false;
			// 성공적으로 추가되었는지 확인용

			
			try{
				Class.forName( DRV );
				conn = DriverManager.getConnection(URL, USR, PWD);
				ps = conn.prepareStatement(insertSQL);
				// ? 매개변수에 들어갈 실제 값을 지정
					ps.setString(1, sj.getName());
					ps.setInt(2, sj.getKor());
					ps.setInt(3, sj.getEng());
					ps.setInt(4, sj.getMat());
				
				int cnt = ps.executeUpdate();
				// 성공적으로 insert 되면 result를 true로
				if (cnt > 0) result = true;
			}catch (Exception ex){
				ex.printStackTrace();
			}finally{
				if (ps != null) 
					try{ps.close();}catch(Exception e){}
				if (conn != null) 
					try{conn.close();}catch(Exception e){}
			}
			
			return result;
		}

		@Override
		public String readSungJuk() {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String result = "";
			
			try{
				Class.forName(DRV);
				conn = DriverManager.getConnection(URL, USR, PWD);
				ps = conn.prepareStatement(selectSQL);
				rs = ps.executeQuery();
				
				// selectSQL 실행결과를 모두 출력
				while(rs.next()){
//					result +=rs.getString(1);
//					result +=rs.getString(2);
//					result +=rs.getString(3);
//					result +=rs.getString(4);
//					result +=rs.getString(5);
//					result +=rs.getString(6);
//					result +=rs.getString(7);
//					result +=rs.getString(8);
//					result +=rs.getString(9);
					for(int i=1;i <= 9; ++i)
						result += rs.getString(i);      // ArrayList로 처리
					result += "\n";
				}
				
			}catch(Exception ex){
			} finally{
				if(rs != null)
					try{rs.close();}catch(Exception e){}
				if(ps != null)
					try{ps.close();}catch(Exception e){}
				if(conn != null)
					try{conn.close();}catch(Exception e){}
			}
			
			return result;
		}

		@Override
		public boolean modifySungJuk(SungJukVO sj) {
			Connection conn = null;
			PreparedStatement ps = null;
			boolean result = false;
			// 성공적으로 수정되었는지 확인용

			
			try{
				Class.forName( DRV );
				conn = DriverManager.getConnection(URL, USR, PWD);
				ps = conn.prepareStatement(updateSQL);
				// ? 매개변수에 들어갈 실제 값을 지정
					ps.setString(1, sj.getName());
					ps.setInt(2, sj.getKor());
					ps.setInt(3, sj.getEng());
					ps.setInt(4, sj.getMat());
					ps.setString(5, sj.getSjno());
				
				int cnt = ps.executeUpdate();
				// 성공적으로 insert 되면 result를 true로 
				if (cnt > 0) result = true;
			}catch (Exception ex){
				ex.printStackTrace();
			}finally{
				if (ps != null) 
					try{ps.close();}catch(Exception e){}
				if (conn != null) 
					try{conn.close();}catch(Exception e){}
			}
			
			
			
			return result;
		}

		@Override
		public boolean removeSungJuk(String key) {
			Connection conn = null;
			PreparedStatement ps = null;
			boolean result = false;
			// 성공적으로 삭제되었는지 확인용
			
			try{
				Class.forName(DRV);
				conn = DriverManager.getConnection(URL,USR,PWD);
				ps = conn.prepareStatement(deleteSQL);
					ps.setString(1, key); // 첫번째 ? 자리에 들어갈 값 지정
				int cnt = ps.executeUpdate();
				// sql문 실행 후 결과 반환
				if (cnt > 0) result = true;
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
				if(ps != null)
					try{ps.close();}catch(Exception e){}
				if(conn != null)
					try{conn.close();}catch(Exception e){}
			}
					
			return result;
		}

	}


